﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace Quản_Lý_Thư_Viện
{
    class SachDataAccess
    {
        private DataProvider _provider = new DataProvider();
        public SachDataAccess()
        {
            _provider.connect();
        }
        public void insertsach(SachInfo info)
        {
            string insertCommand = "insert into dbo.SACH(MASACH, MADAUSACH, TINHTRANG) values('"
                                + info.Masach + "'," + "N'" + info.Madausach + "'," + "N'"
                                + info.Tinhtrang + "')";

            _provider.executeNonQuery(insertCommand);
        }
        public void updatesach(SachInfo info)
        {
            string updateCommnad = "Update dbo.SACH Set MADAUSACH= N'"
                            + info.Madausach + "', TINHTRANG =N'"
                            + info.Tinhtrang
                            + "' Where MASACH = '" + info.Masach + "'";
            _provider.executeNonQuery(updateCommnad);
        }
        public void updatemuonsach(SachInfo info)
        {
            string updateCommand = "Update dbo.SACH set TINHTRANG = 'Da cho muon' where MASACH = '"+info.Masach+"'";
            _provider.executeNonQuery(updateCommand);
        }
        public void updatetrasach(SachInfo info)
        {
            string updateCommand = "Update dbo.SACH set TINHTRANG = 'Dang ranh' where MASACH = '" + info.Masach + "'";
            _provider.executeNonQuery(updateCommand);
        }

        public void updatehay(SachInfo info)
        {
            string updateCommand = "Update dbo.DAUSACH set THICH = " + info.Hay + " where MADAUSACH =  '" + info.Madausach + "'";
            _provider.executeNonQuery(updateCommand);
        }

        public void deletesach(SachInfo info)
        {
            string deleteCommand = "DELETE FROM dbo.SACH WHERE MASACH = '" + info.Masach + "'";
            _provider.executeNonQuery(deleteCommand);
        }
        public void deletetemp(SachInfo info)
        {
            string temp = "Delete from dbo.SACH where MADAUSACH = '" + frmNhapDauSach.a + "'";
            _provider.executeNonQuery(temp);
        }

        public DataTable LayDSsach()
        {
            return _provider.GetData("select * from dbo.SACH order by MASACH desc");
        }
        public DataTable Laysachvandausach()
        {
            return _provider.GetData("select dbo.DAUSACH.MADAUSACH,MATHELOAI,TENSACH,NXB,NAMXB,TRIGIA,MASACH,TINHTRANG,HINHANH from dbo.DAUSACH,dbo.SACH where dbo.DAUSACH.MADAUSACH=dbo.SACH.MADAUSACH");
            //string m = "select dbo.DAUSACH.MADAUSACH,MATHELOAI,TENSACH,NXB,NAMXB,TRIGIA,MASACH,TINHTRANG from dbo.DAUSACH,dbo.SACH where dbo.DAUSACH.MADAUSACH=dbo.SACH.MADAUSACH";
        }
        public DataTable timsachtheoten()
        {
            return _provider.GetData("select * from SACH where MASACH like ''");
        }
        public DataTable laythongtinsach()
        {
            return _provider.GetData("Select DAUSACH.MADAUSACH, TENSACH , NXB , NAMXB ,THELOAISACH.TENTHELOAI , TRIGIA , HINHANH from DAUSACH,THELOAISACH where THELOAISACH.MATHELOAI = DAUSACH.MATHELOAI and MADAUSACH = '" + frmChinh.mds + "'");
        }

        public DataTable sachtheotacgia()
        {
            return _provider.GetData("select dbo.DAUSACH.MADAUSACH,MATHELOAI,TENSACH,NXB,NAMXB,TRIGIA,MASACH,TINHTRANG,HINHANH from dbo.DAUSACH , dbo.SACH,dbo.CONHIEU,dbo.TACGIA where dbo.DAUSACH.MADAUSACH = dbo.SACH.MADAUSACH and dbo.CONHIEU.MATG = dbo.TACGIA.MATG and dbo.CONHIEU.MADAUSACH = dbo.SACH.MADAUSACH and TENTG = '" + frmChinh.hihi + "' and TINHTRANG = 'Dang ranh'");
        }
        public DataTable sachtheotheloai()
        {
            return _provider.GetData("select dbo.DAUSACH.MADAUSACH,MATHELOAI,TENSACH,NXB,NAMXB,TRIGIA,MASACH,TINHTRANG,HINHANH from dbo.DAUSACH , dbo.SACH,dbo.CONHIEU,dbo.TACGIA where dbo.DAUSACH.MADAUSACH = dbo.SACH.MADAUSACH and dbo.THELOAISACH.MATHELOAI = dbo.DAUSACH.MATHELOAI and TENTHELOAI = '" + frmChinh.hihi + "' and TINHTRANG = 'Dang ranh'");
        }
        public DataTable timsach()
        {
            return _provider.GetData("select dbo.DAUSACH.MADAUSACH,MATHELOAI,TENSACH,NXB,NAMXB,TRIGIA,MASACH,TINHTRANG,HINHANH from dbo.DAUSACH,dbo.SACH where dbo.DAUSACH.MADAUSACH=dbo.SACH.MADAUSACH and TENSACH like " + "N'" + frmChinh.hihi + "%' and TINHTRANG = 'Dang ranh'");
        }
        public DataTable laysach()
        {
            return _provider.GetData("select MASACH from dbo.SACH where MADAUSACH = '" + frmNhapDauSach.madausachtemp + "'");
        }
        public DataTable top4sachmuon()
        {
            return _provider.GetData("SELECT TOP 4 TENSACH, COUNT (B.MASACH) AS SO_LAN_MUON   FROM SACH A, CT_PMS B, DAUSACH C WHERE A.MASACH = B.MASACH AND A.MADAUSACH = C.MADAUSACH GROUP BY TENSACH ORDER BY SO_LAN_MUON desc");
        }
        public DataTable top4sachmuonhinhanh()
        {
            return _provider.GetData("select HINHANH from DAUSACH where TENSACH in (SELECT TOP 4 TENSACH  FROM SACH A, CT_PMS B, DAUSACH C WHERE A.MASACH = B.MASACH AND A.MADAUSACH = C.MADAUSACH GROUP BY TENSACH ORDER BY COUNT (B.MASACH) desc)");
        }
        public DataTable layvote()
        {
            return _provider.GetData("select THICH,DAUSACH.MADAUSACH from DAUSACH ,SACH where DAUSACH.MADAUSACH = SACH.MADAUSACH and MASACH = '" + frmPhieuTraSach.masachtemp + "'");
        }
        public DataTable tims()
        {
            return _provider.GetData("select * from SACH where MASACH like '" + frmNhapSach.timsach + "%'");
        }
        public DataTable top4votemds()
        {
            return _provider.GetData("select TOP 4 MADAUSACH from SACH order by THICH desc");
        }

    }
}
